﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DataManagement;
using MLP;
using System.Windows.Forms.DataVisualization.Charting;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.IO;

namespace NeuroPrev
{
    public partial class FMain : Form
    {
        private static string _ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=NeuroPrev;Integrated Security=TRUE";

        public FMain()
        {
            InitializeComponent();
            _Writer = new ControlWriter(this.rtxtOutput, this);
            Console.SetOut(_Writer);
        }

        #region Attributes
        private ControlWriter _Writer;
        private List<List<double>> _Values;
        private System.Data.DataTable _DataTable;

        private const string ID = "Id";
        private const string DESCRIPTION = "Description";
        private const string IMPORTATION_DATE = "ImportationDate";
        private const string ITEM_HOLDER = "ItemHolder";
        private const string EXPORT = "Export";
        private const string WAIT_LOADING = "Aguarde carregando";
        #endregion
        
        #region Private Methods
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            _LoadData();
        }

        private void _LoadData()
        {
            _EnableBusy(WAIT_LOADING);
            bwLoadData.RunWorkerAsync();
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Erro ao liberar recursos do excel " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void _EnableBusy(string text)
        {
            statusLabel.Image = global::NeuroPrev.Properties.Resources.ajax_loader;
            statusLabel.ImageAlign = System.Drawing.ContentAlignment.MiddleRight;
            statusLabel.Text = text;
            tabControl.Enabled = false;
        }

        private void _DisableBusy(string text)
        {
            statusLabel.Image = null;
            statusLabel.Text = text;
            tabControl.Enabled = true;
        }
        #endregion

        #region Events
        private void btnLoadCSV_Click(object sender, EventArgs e)
        {
              DialogResult dr = this.ofdLoadCSV.ShowDialog();

              if (dr == System.Windows.Forms.DialogResult.OK)
              {
                  this.txtCSVPath.Text = ofdLoadCSV.FileName;
              }
        }

        private void btnExportData_Click(object sender, EventArgs e)
        {
            DialogResult dr = this.sfdExportData.ShowDialog();
            if (dr == System.Windows.Forms.DialogResult.OK && !string.IsNullOrWhiteSpace(sfdExportData.FileName) && !string.IsNullOrWhiteSpace(txtCSVPath.Text))
            {
                _EnableBusy("Aguarde exportando dados");
                bwExportData.RunWorkerAsync(new object[] {txtCSVPath.Text, sfdExportData.FileName});
            }
        }

        private void btnGenerateReport_Click(object sender, EventArgs e)
        {
            DialogResult dr = this.sfdExportReport.ShowDialog();
            if (dr == System.Windows.Forms.DialogResult.OK && !string.IsNullOrWhiteSpace(sfdExportReport.FileName) &&
                _Values != null && !string.IsNullOrWhiteSpace(txtCSVPath.Text))
            {
                _EnableBusy("Aguarde gerando relatório");
                bwExportReport.RunWorkerAsync(new object[] {sfdExportReport.FileName, txtCSVPath.Text});
            }
        }

        private void btnTrain_Click(object sender, EventArgs e)
        {
            _EnableBusy("Aguarde treinando rede neural");
            rtxtOutput.Clear();
            chart.Series["Realizado"].Points.Clear();
            chart.Series["Previsto"].Points.Clear();
            _Values = null;
            bwTrain.RunWorkerAsync();
        }

        private void grid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                DataRow datarow = _DataTable.Rows[e.RowIndex];
                Record r = datarow[2] as Record;

                DialogResult dr = this.sfdExportData.ShowDialog();
                if (dr == System.Windows.Forms.DialogResult.OK && !string.IsNullOrWhiteSpace(sfdExportData.FileName))
                {
                    _EnableBusy("Aguarde exportando dados");
                    bwExportRecord.RunWorkerAsync(new object[] { r, sfdExportData.FileName });
                }
            }
        }

        private void _BuildGridColumns()
        {
            grid.Columns.Clear();

            _DataTable = new System.Data.DataTable();

            //DataGridViewTextBoxColumn colId = new DataGridViewTextBoxColumn();
            //colId.ReadOnly = true;
            //colId.Name = ID;
            //colId.HeaderText = string.Empty;
            //colId.DataPropertyName = ID;
            //colId.Visible = false;
            //grid.Columns.Add(colId);

            //_DataTable.Columns.Add(ID, typeof(int));

            DataGridViewTextBoxColumn colDescription = new DataGridViewTextBoxColumn();
            colDescription.ReadOnly = true;
            colDescription.Name = DESCRIPTION;
            colDescription.HeaderText = "Descrição do arquivo";
            colDescription.DataPropertyName = DESCRIPTION;
            colDescription.Visible = true;
            colDescription.Width = 300;
            grid.Columns.Add(colDescription);

            _DataTable.Columns.Add(DESCRIPTION, typeof(string));

            DataGridViewTextBoxColumn colImportationDate = new DataGridViewTextBoxColumn();
            colImportationDate.ReadOnly = true;
            colImportationDate.Name = IMPORTATION_DATE;
            colImportationDate.HeaderText = "Data Importação";
            colImportationDate.DataPropertyName = IMPORTATION_DATE;
            colImportationDate.Width = 30;
            colImportationDate.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            colImportationDate.Visible = true;
            grid.Columns.Add(colImportationDate);

            _DataTable.Columns.Add(IMPORTATION_DATE, typeof(DateTime));

            DataGridViewTextBoxColumn colItemHolder = new DataGridViewTextBoxColumn();
            colItemHolder.ReadOnly = true;
            colItemHolder.Name = ITEM_HOLDER;
            colItemHolder.HeaderText = string.Empty;
            colItemHolder.DataPropertyName = ITEM_HOLDER;
            colItemHolder.Visible = false;
            grid.Columns.Add(colItemHolder);

            _DataTable.Columns.Add(ITEM_HOLDER, typeof(Record));

            DataGridViewButtonColumn colExport = new DataGridViewButtonColumn();
            colExport.ReadOnly = true;
            colExport.Name = EXPORT;
            colExport.HeaderText = "Exportar";
            colExport.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            colExport.Width = 10;
            colExport.DataPropertyName = EXPORT;
            colExport.Visible = true;
            grid.Columns.Add(colExport);
        }

        private void _BuildDataSet(List<Record> records)
        {
            DataRow dr = null;
            object[] item = null;
            for (int i = 0; i < records.Count; i++)
            {
                dr = _DataTable.NewRow();
                item = new object[]
                {
                    //records[i].Id,
                    records[i].SheetDescription,
                    records[i].ImportationDate,
                    records[i]
                };

                dr.ItemArray = item;
                _DataTable.Rows.Add(dr);
            }

            grid.DataSource = _DataTable;
        }

        private void tabControl_DrawItem(object sender, DrawItemEventArgs e)
        {
            Graphics g = e.Graphics;
            Brush _textBrush;

            TabPage _tabPage = tabControl.TabPages[e.Index];

            System.Drawing.Rectangle _tabBounds = tabControl.GetTabRect(e.Index);

            if (e.State == DrawItemState.Selected)
            {
                _textBrush = new SolidBrush(Color.Black);
                g.FillRectangle(Brushes.Gray, e.Bounds);
            }
            else
            {
                _textBrush = new System.Drawing.SolidBrush(e.ForeColor);
                e.DrawBackground();
            }

            System.Drawing.Font _tabFont = new System.Drawing.Font("Arial", (float)10.0, FontStyle.Bold, GraphicsUnit.Pixel);

            StringFormat _stringFlags = new StringFormat();
            _stringFlags.Alignment = StringAlignment.Center;
            _stringFlags.LineAlignment = StringAlignment.Center;
            g.DrawString(_tabPage.Text, _tabFont, _textBrush, _tabBounds, new StringFormat(_stringFlags));
        }
        #endregion

        #region BackgroundWorkers Events
        private void bwTrain_DoWork(object sender, DoWorkEventArgs e)
        {
            string path = txtCSVPath.Text;

            double inputA = double.Parse(txtInputA.Text);
            double inputB = double.Parse(txtInputB.Text);
            double outputA = double.Parse(txtOutputA.Text);
            double outputB = double.Parse(txtOutputB.Text);

            double trainSize = double.Parse(mskTrain.Text) / 10000;
            double validationSize = double.Parse(mskValidation.Text) / 10000;
            double testSize = double.Parse(mskTest.Text) / 10000;

            double alfa = double.Parse(mskAlfa.Text) / 100;
            double beta = double.Parse(mskBeta.Text) / 100;

            int maxCicles = (int)nudMaxCicles.Value;
            int hiddenNeurons = (int)nudHiddenNeurons.Value;

            int inputLag = (int)nudInputLag.Value;
            int outputLag = (int)nudOutputLag.Value;

            Console.WriteLine("Carregando dados da planilha...");

            DataBuilder db = new DataBuilder(@path);
            db.LagData(inputLag, outputLag);

            Data[] set = db.BuildDataSet();

            DataProvider prov = new DataProvider(set, EExecutionType.Predction, 1, trainSize, validationSize, testSize);

            if (chkApplyLogToData.Checked)
            {
                Console.WriteLine("Aplicando tranformação logarítmica");
                prov.ApplyLogToData();
            }

            if (chkNormalizeData.Checked)
            {
                Console.WriteLine("Normalizando dados...");
                prov.NormalizeData(inputA, inputB, outputA, outputB);
            }

            if (chkShuffleData.Checked)
            {
                Console.WriteLine("Embaralhando dados...");
                prov.ShuffleDataSet();
            }

            Console.WriteLine("Dividindo conjuntos de dados...");
            prov.SplitData();

            double[][] entradasTreinamento = null;
            double[][] saidasTreinamento = null;
            double[][] entradasValidacao = null;
            double[][] saidasValidacao = null;
            double[][] entradasTeste = null;
            double[][] saidasTeste = null;

            entradasTreinamento = new double[prov.TrainSetLines][];
            saidasTreinamento = new double[prov.TrainSetLines][];
            for (int i = 0; i < prov.TrainSetLines; i++)
            {
                entradasTreinamento[i] = new double[prov.TrainSet[i].Input.Length];
                saidasTreinamento[i] = new double[prov.TrainSet[i].Output.Length];

                for (int j = 0; j < prov.TrainSet[i].Input.Length; j++)
                    entradasTreinamento[i][j] = prov.TrainSet[i].Input[j];

                for (int j = 0; j < prov.TrainSet[i].Output.Length; j++)
                    saidasTreinamento[i][j] = prov.TrainSet[i].Output[j];
            }

            entradasValidacao = new double[prov.ValidationSetLines][];
            saidasValidacao = new double[prov.ValidationSetLines][];
            for (int i = 0; i < prov.ValidationSetLines; i++)
            {
                entradasValidacao[i] = new double[prov.ValidationSet[i].Input.Length];
                saidasValidacao[i] = new double[prov.ValidationSet[i].Output.Length];

                for (int j = 0; j < prov.ValidationSet[i].Input.Length; j++)
                    entradasValidacao[i][j] = prov.ValidationSet[i].Input[j];

                for (int j = 0; j < prov.ValidationSet[i].Output.Length; j++)
                    saidasValidacao[i][j] = prov.ValidationSet[i].Output[j];
            }

            entradasTeste = new double[prov.TestSetlines][];
            saidasTeste = new double[prov.TestSetlines][];
            for (int i = 0; i < prov.TestSetlines; i++)
            {
                entradasTeste[i] = new double[prov.TestSet[i].Input.Length];
                saidasTeste[i] = new double[prov.TestSet[i].Output.Length];

                for (int j = 0; j < prov.TestSet[i].Input.Length; j++)
                    entradasTeste[i][j] = prov.TestSet[i].Input[j];

                for (int j = 0; j < prov.TestSet[i].Output.Length; j++)
                    saidasTeste[i][j] = prov.TestSet[i].Output[j];
            }

            Console.WriteLine("Treinando Rede neural...");
            RedeMLP mlp;
            mlp = new RedeMLP(prov,
                entradasTreinamento,//array de entradas de treinamento
                saidasTreinamento,//array de saidas de treinamento
                entradasValidacao,//array de entradas de validacao
                saidasValidacao,//array de saidas de validacao
                alfa,//valor de alpha
                beta,//valor de beta
                maxCicles,//maximo de ciclos
                hiddenNeurons,//quatidade de neuronios escondida
                EnumTipoExecucao.Previsao,//booleano para definir se é previsao(true) ou classificacao(false)
                Algoritmos.BACKPROPAGATION);//constante q define o algoritmo a ser utilizado

            Console.WriteLine("Testando Rede neural...");
            mlp.testar(entradasTeste, saidasTeste);

            e.Result = mlp;
        }

        private void bwTrain_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            RedeMLP mlp = e.Result as RedeMLP;
            _Values = mlp.getReaisPrevistos();

            Console.WriteLine("Gerando gráfico de previsão...");

            for (int j = 0; j < _Values[0].Count; j++)
            {
                chart.Series["Realizado"].Points.AddXY
                               (j, _Values[0][j]);
                chart.Series["Previsto"].Points.AddXY
                                (j, _Values[1][j]);
            }

            Console.WriteLine("Treino concluído");
            _DisableBusy("Treino concluído");
        }
        
        private void bwExportData_DoWork(object sender, DoWorkEventArgs e)
        {
            string sourcePath = ((object[])e.Argument)[0] as string;

            double inputA = double.Parse(txtInputA.Text);
            double inputB = double.Parse(txtInputB.Text);
            double outputA = double.Parse(txtOutputA.Text);
            double outputB = double.Parse(txtOutputB.Text);

            DataBuilder builder = new DataBuilder(@sourcePath);             
            builder.LagData(1, 1);

            Data[] set = builder.BuildDataSet();

            DataProvider prov = new DataProvider(set, EExecutionType.Predction, 1, 0.5, 0.25, 0.25);

            if (chkNormalizeData.Checked)
                prov.NormalizeData(inputA, inputB, outputA, outputB);

            if (chkApplyLogToData.Checked)
                prov.ApplyLogToData();

            Data[] normalizedSet = prov.DataSet;
            List<double> normalizedSeries = new List<double>();

            for (int i = 0; i < normalizedSet.Length; i++)
            {
                normalizedSeries.Add(normalizedSet[i].Input[0]);
            }

            builder = new DataBuilder(normalizedSeries);

            e.Result = new object[] {((object[])e.Argument)[1], builder};
        }

        private void bwExportData_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {            
            string exportationPath = ((object[])e.Result)[0] as string;
            DataBuilder builder =  ((object[])e.Result)[1] as DataBuilder;

            builder.SaveTimeSeriesToFile(@exportationPath);
            _DisableBusy(string.Empty);
            MessageBox.Show(this, "Exportação concluída", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void bwExportReport_DoWork(object sender, DoWorkEventArgs e)
        {
            string exportationPath = ((object[])e.Argument)[0] as string;
            string path = ((object[])e.Argument)[1] as string;

            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = xlWorkBook.Worksheets[1];

            //add data 
            xlWorkSheet.Cells[1, 1] = "Real";
            xlWorkSheet.Cells[1, 2] = "Previsto";

            for (int i = 0; i < _Values[0].Count; i++)
            {
                xlWorkSheet.Cells[i + 2, 1] = _Values[0][i];
                xlWorkSheet.Cells[i + 2, 2] = _Values[1][i]; 
            }
               
            Microsoft.Office.Interop.Excel.Range chartRange;

            Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(80, 80, 900, 250);
            Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;

            int index = _Values[0].Count + 1;
            chartRange = xlWorkSheet.get_Range("A1", "b" + index);
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers;
            
            xlWorkBook.SaveAs(@exportationPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            FileInfo fi = new FileInfo(path);
            FileStream fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
            BinaryReader rdr = new BinaryReader(fs);
            byte[] fileData = rdr.ReadBytes((int)fs.Length);
            rdr.Close();
            fs.Close();
            SqlConnection conn = new SqlConnection(_ConnectionString);

            try
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "InserirRegistro";
                    cmd.Parameters.Add("@DescricaoPlanilha", SqlDbType.VarChar).Value = Path.GetFileName(path);
                    cmd.Parameters.Add("@DataImportacao", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@Dados", SqlDbType.Image, fileData.Length).Value = fileData;

                    cmd.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }       
        }

        private void bwExportReport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            _DisableBusy(string.Empty);
            MessageBox.Show(this, "Relatatório criado", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            bwLoadData.RunWorkerAsync();
        }
        
        private void bwLoadData_DoWork(object sender, DoWorkEventArgs e)
        {
            string idx_Id = "Id";
            string idx_DescricaoPlanilha = "DescricaoPlanilha";
            string idx_DataImportacao = "DataImportacao";
            string idx_Dados = "Dados";

            List<Record> records = new List<Record>();

            SqlConnection conn = new SqlConnection(_ConnectionString);

            try
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "GetRegistros";
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        records.Add(new Record()
                        {
                              Id = (int)dr[idx_Id],
                              SheetDescription = (string)dr[idx_DescricaoPlanilha],
                              ImportationDate = (DateTime)dr[idx_DataImportacao],
                              Data = (byte[])dr[idx_Dados]
                        });
                    }
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            e.Result = records;
        }

        private void bwLoadData_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            _BuildGridColumns();
            _BuildDataSet(e.Result as List<Record>);
            this.grid.AutoResizeColumns();

            _DisableBusy("Dados carregados");

        }
                
        private void bwExportRecord_DoWork(object sender, DoWorkEventArgs e)
        {
            Record r = ((object[])e.Argument)[0] as Record;
            string path = ((object[])e.Argument)[1] as string;

            using (System.IO.FileStream fs = new System.IO.FileStream(path, FileMode.Create, FileAccess.Write, FileShare.Write))
            {
                fs.Write(r.Data, 0, r.Data.Length);
                fs.Flush();
                fs.Close();
            }
        }

        private void bwExportRecord_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            _DisableBusy(string.Empty);
            
            if(e.Error == null)
                MessageBox.Show(this, "Exportação concluída", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        #endregion

        private void rtxtOutput_TextChanged(object sender, EventArgs e)
        {
            rtxtOutput.SelectionStart = rtxtOutput.Text.Length; //Set the current caret position at the end
            rtxtOutput.ScrollToCaret(); //Now scroll it automatically
        }
    }
}
